package org.zero.db;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * dbutil 辅助类
 * 依赖 Apache Commons Dbutils
 *
 * @author Zero
 */
public class DbutilsHelper {
    private DbutilsHelper() {
    }

    private static final QueryRunner QUERY_RUNNER = new QueryRunner();

    /**
     * 增加数据并返回主键
     *
     * @param connection
     * @param sql
     * @param args
     * @return
     */
    public static Long insert(Connection connection, String sql, Object... args) throws SQLException {
        return QUERY_RUNNER.insert(connection, sql, new ScalarHandler<Long>(), args);
    }

    /**
     * 增删改
     *
     * @param connection
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static int update(Connection connection, String sql, Object... args) throws SQLException {
        return QUERY_RUNNER.update(connection, sql, args);
    }

    /**
     * 查询一行多列，封装到实体类
     * 注意：没法进行宽松绑定，即无法映射数据库有”_“的字段到Java实体类
     *
     * @param <T>
     * @param connection
     * @param type
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static <T> T getBean(Connection connection, String sql, Class<T> type, Object... args) throws SQLException {
        return QUERY_RUNNER.query(connection, sql, new BeanHandler<T>(type), args);
    }

    /**
     * 查询多行多列，封装到实体类集合
     *
     * @param <T>
     * @param connection
     * @param sql
     * @param type
     * @param args
     * @return
     * @throws SQLException
     */
    public static <T> List<T> getBeanList(Connection connection, String sql, Class<T> type, Object... args) throws SQLException {
        return QUERY_RUNNER.query(connection, sql, new BeanListHandler<T>(type), args);
    }

    /**
     * 查询一行多列，封装到 Object 数组
     *
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static Object[] getObjectArray(Connection connection, String sql, Object... args) throws SQLException {
        return QUERY_RUNNER.query(connection, sql, new ArrayHandler(), args);
    }

    /**
     * 查询多行多列，封装到 List<Object[]>
     *
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static List<Object[]> getObjectArrayList(Connection connection, String sql, Object... args) throws SQLException {
        return QUERY_RUNNER.query(connection, sql, new ArrayListHandler(), args);
    }

    /**
     * 查询一行多列，封装到 Map<String, Object>
     *
     * @param connection
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static Map<String, Object> getMap(Connection connection, String sql, Object... args) throws SQLException {
        return QUERY_RUNNER.query(connection, sql, new MapHandler(), args);
    }

    /**
     * 查询多行多列，封装到 Map<String, Map<String, Object>>
     *
     * @param connection
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static Map<String, Map<String, Object>> getKeyedMap(Connection connection, String sql, Object... args)
            throws SQLException {
        return QUERY_RUNNER.query(connection, sql, new KeyedHandler<String>(), args);
    }

    /**
     * 查询多行多列，封装到 List<Map<String, Object>>
     *
     * @param connection
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static List<Map<String, Object>> getMapList(Connection connection, String sql, Object... args)
            throws SQLException {
        return QUERY_RUNNER.query(connection, sql, new MapListHandler(), args);
    }

    /**
     * 查询一行一列，封装到指定类型
     * 注意：不支持jdk 8的时间API；查询数据表行数时只能转成long型数据
     *
     * @param <T>
     * @param connection
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static <T> T getObject(Connection connection, String sql, Class<T> type, Object... args)
            throws SQLException {
        return QUERY_RUNNER.query(connection, sql, new ScalarHandler<T>(), args);
    }

    /**
     * 查询多行一列，封装到List
     * 注意：不支持jdk 8的时间API
     *
     * @param <T>
     * @param connection
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static <T> List<T> getObjectList(Connection connection, String sql, Class<T> type, Object... args) throws SQLException {
        return QUERY_RUNNER.query(connection, sql, new ColumnListHandler<T>(), args);
    }
}
